*********************************************************
* Setup Code (Part A) for:
*
* The Value of Ratings: Evidence from their Introduction in Securities Markets
* Authors: Asaf Bernstein, Carola Frydman, Eric Hilt
*
* Code last updated: 5/23/25
*
*************************************************************

*************************************************************
* I. Initial Code Settings/Directories
*************************************************************

clear all
* Set to folder of where do file is saved. Change if necessary.
cd "`=substr(c(current_do), 1, indexnot(c(current_do), "/", -1))'"

*************************************************************
* II. Compute surprises
*************************************************************

use "SurpriseData.dta", clear

*Compute average yields prior to ratings at the bond level

    destring yield, force replace
    destring duration, force replace
	gen wk_since = floor((date_trans + 18514)/7)
	bys id_issue: egen PreMean_YTM_Last_id_issue = mean(yield) if wk_since>=-22 & wk_since<=-1
	bysort id_issue_not_detailed : egen PreMean_YTM_Last_nd = mean(PreMean_YTM_Last_id_issue)
	bys id_issue_not_detailed: egen PreMean_YTM_Last_tmp = mean(yield) if wk_since>=-22 & wk_since<=-1
	bys id_issue_not_detailed: egen PreMean_Spread_pct_tmp = mean(spread_pct_wis) if wk_since>=-22 & wk_since<=-1
	bys id_issue_not_detailed: egen PreMean_YTM_Last = max(PreMean_YTM_Last_tmp)
	bys id_issue_not_detailed: egen PreMean_Spread_pct = max(PreMean_Spread_pct_tmp)
	drop PreMean_YTM_Last_tmp PreMean_Spread_pct_tmp
	
*************************************************************
* Part 1: Bond (Issue) level Surprises
*************************************************************	
	
* Calculate quartiles by pre-rating mean yields (weighting by one observation per bond)

	cap drop not_duplicate
	bys id_issue: gen nnn = _n
	gen not_duplicate = nnn == 1
	drop nnn	
	egen PreMeanYTM_Last_Qs_sub = xtile(PreMean_YTM_Last_nd) if not_duplicate == 1, nq(4)
	bys id_issue: egen PreMeanYTM_Last_Qs = max(PreMeanYTM_Last_Qs_sub)
	drop PreMeanYTM_Last_Qs_sub
	
* Find median rating in each bucket (weighting by one observation per issuer)

	egen medianRatingInYB_1 = median(rating_num) if not_duplicate == 1 & PreMeanYTM_Last_Qs == 1
	egen medianRatingInYB_2 = median(rating_num) if not_duplicate == 1 & PreMeanYTM_Last_Qs == 2
	egen medianRatingInYB_3 = median(rating_num) if not_duplicate == 1 & PreMeanYTM_Last_Qs == 3
	egen medianRatingInYB_4 = median(rating_num) if not_duplicate == 1 & PreMeanYTM_Last_Qs == 4
	
* Create a variable that is the expected rating for each quartile

	gen medianRatingInYB_s = medianRatingInYB_1 if PreMeanYTM_Last_Qs == 1
	replace medianRatingInYB_s = medianRatingInYB_2 if PreMeanYTM_Last_Qs == 2
	replace medianRatingInYB_s = medianRatingInYB_3 if PreMeanYTM_Last_Qs == 3
	replace medianRatingInYB_s = medianRatingInYB_4 if PreMeanYTM_Last_Qs == 4
	bys PreMeanYTM_Last_Qs : egen ExpectedRating_median = max(medianRatingInYB_s)	
	drop medianRatingInYB_1 medianRatingInYB_2 medianRatingInYB_3 medianRatingInYB_4
	
* Construct surprise variable

	gen surprise_issue_A_YTM_Last = ExpectedRating_median - rating_num

	*Negative surprise
	gen bad_issue_surp_A_YTM_Last = .
	replace bad_issue_surp_A_YTM_Last = 1 if surprise_issue_A_YTM_Last < 0
	replace bad_issue_surp_A_YTM_Last = 0 if surprise_issue_A_YTM_Last >= 0
	replace bad_issue_surp_A_YTM_Last = . if surprise_issue_A_YTM_Last == .		
	drop PreMeanYTM_Last_Qs medianRatingInYB_* ExpectedRating_median surprise_issue_A_YTM_Last
	
*************************************************************
* Part 2: Firm (Issuer) level Surprises
*************************************************************
	
* Calculate quartiles by pre-rating mean yields (weighting by one observation per firm)

	bys id_issuer_1: gen nnn = _n
	gen not_duplicate_issuer = nnn == 1
	drop nnn
	bys id_issuer_1: egen mean_issuer_PM_YTM_Last_sub = mean(PreMean_YTM_Last_nd) if not_duplicate == 1
	bys id_issuer_1: egen mean_issuer_PM_YTM_Last = max(mean_issuer_PM_YTM_Last_sub)
	bys id_issuer_1: egen median_rating_sub = median(rating_num) if not_duplicate == 1
	bys id_issuer_1: egen median_rating = max(median_rating_sub)
	cap drop median_rating_sub
	egen PreMeanYTM_Last_Qs_sub = xtile(mean_issuer_PM_YTM_Last) if not_duplicate_issuer == 1, nq(4)
	bys id_issuer_1: egen PreMeanYTM_Last_Qs = max(PreMeanYTM_Last_Qs_sub)	
	drop PreMeanYTM_Last_Qs_sub
	
* Find mean/median rating in each bucekt (weighting by one observation per issuer)

	egen medianRatingInYB_1 = median(median_rating) if not_duplicate_issuer == 1 & PreMeanYTM_Last_Qs == 1
	egen medianRatingInYB_2 = median(median_rating) if not_duplicate_issuer == 1 & PreMeanYTM_Last_Qs == 2
	egen medianRatingInYB_3 = median(median_rating) if not_duplicate_issuer == 1 & PreMeanYTM_Last_Qs == 3
	egen medianRatingInYB_4 = median(median_rating) if not_duplicate_issuer == 1 & PreMeanYTM_Last_Qs == 4
	
*Create a variable that is the expected rating for each quartile

	gen medianRatingInYB_s = medianRatingInYB_1 if PreMeanYTM_Last_Qs == 1
	replace medianRatingInYB_s = medianRatingInYB_2 if PreMeanYTM_Last_Qs == 2
	replace medianRatingInYB_s = medianRatingInYB_3 if PreMeanYTM_Last_Qs == 3
	replace medianRatingInYB_s = medianRatingInYB_4 if PreMeanYTM_Last_Qs == 4
	bys PreMeanYTM_Last_Qs : egen ExpectedRating_median = max(medianRatingInYB_s)	
	drop medianRatingInYB_1 medianRatingInYB_2 medianRatingInYB_3 medianRatingInYB_4
	
* Construct surprise variable

	gen surprise_RRissuer_A_med_YTM_Last = ExpectedRating_median - median_rating

	* Negative surprise
	gen bad_RRissuer_surp_A_YTM_Last = .	
	replace bad_RRissuer_surp_A_YTM_Last = 1 if surprise_RRissuer_A_med_YTM_Last < 0
	replace bad_RRissuer_surp_A_YTM_Last = 0 if surprise_RRissuer_A_med_YTM_Last >= 0
	replace bad_RRissuer_surp_A_YTM_Last = . if surprise_RRissuer_A_med_YTM_Last == .

	keep id_issue id_issue_not_detailed bad_RRissuer_surp_A_YTM_Last median_rating mean_issuer_PM_YTM_Last PreMeanYTM_Last_Qs bad_issue_surp_A_YTM_Last PreMean_YTM_Last PreMean_Spread_pct

	duplicates drop
	
    tempfile surprises
	save `surprises'	

*************************************************************
* III. Load in data
*************************************************************

use "SurpriseData.dta", clear

destring yield, force replace

destring duration, force replace

	merge m:1 id_issue using `surprises'
	keep if _merge==3
	drop _merge
	
*************************************************************
* IV. Define Variables
*************************************************************

* Main interactions
	
    gen Post = date_trans>=-18514	
	
    egen date_bin = group(date_trans)	
	
    gen neg_post = bad_RRissuer_surp_A_YTM_Last*Post	

    gen wk_since = floor((date_trans + 18514)/7)	
    keep if wk_since <= 52 & wk_since>=-52 & yield!=. & date_bin!=. & rating_num!=.

    gen weeks_since = 0
    replace weeks_since = wk_since if wk_since>0
    gen neg_weeks_since =  weeks_since * bad_RRissuer_surp_A_YTM_Last

    gen weeks = wk_since
    gen neg_weeks = wk_since * bad_RRissuer_surp_A_YTM_Last
    gen neg_weeks_post = wk_since * bad_RRissuer_surp_A_YTM_Last * Post

    gen weeks_since_negsurp_issue =  weeks_since * bad_issue_surp_A_YTM_Last

* Underwriters on board interactions

    gen big_numuw_top10 = numuw_top10>=3		
    gen small_numuw_top10 = numuw_top10==0

    gen neg_wks_m_undw = neg_weeks_since * big_numuw_top10	
    gen wks_m_undw = weeks_since * big_numuw_top10

    gen weeks_negsurp_small_numuw_top10 = neg_weeks_since * small_numuw_top10	
    gen weeks_small_numuw_top10 = weeks_since * small_numuw_top10

*Create week interactions for regression in figure

    gen week_trans = week(date_trans)

    cap drop date_dum_*
    cap drop bd_date_dum_*

    local prev_month " "
    forval yr = 1908/1910 {
	   forval week = 1/52 {
		if (`yr'==1909 & `week'<=52) | (`yr'==1908 & `week'>=1) |  (`yr'==1910 & `week'<=52) {
			qui gen date_dum_`yr'_`week' = (year_trans==`yr' & week_trans==`week')
			qui gen bd_date_dum_`yr'_`week' = bad_RRissuer_surp_A_YTM_Last * date_dum_`yr'_`week'
			qui label var bd_date_dum_`yr'_`week' " "			
			
			if (`week'>=1 & `week'<=5) {
				local month "Jan"
			}
			if (`week'>=6 & `week'<=9) {
				local month "Feb"
			}				
			if (`week'>=10 & `week'<=13) {
				local month "Mar"
			}
			if (`week'>=14 & `week'<=18) {
				local month = "Apr"
			}
			if (`week'>=19 & `week'<=22) {
				local month "May"
			}
			if (`week'>=23 & `week'<=26) {
				local month "Jun"
			}				
			if (`week'>=27 & `week'<=31) {
				local month "Jul"
			}
			if (`week'>=32 & `week'<=35) {
				local month "Aug"
			}
			if (`week'>=36 & `week'<=39) {
				local month "Sep"
			}
			if (`week'>=40 & `week'<=44) {
				local month "Oct"
			}				
			if (`week'>=45 & `week'<=48) {
				local month "Nov"
			}
			if (`week'>=49 & `week'<=52) {
				local month "Dec"
			}		
			
			if "`prev_month'"~="`month'" {
				qui label var bd_date_dum_`yr'_`week' "`yr' `month' -"			
			}
			
			if `yr'~=1909 | "`month'"~="Jan" | `week'~=1{
				local prev_month `month'
			}
		   }
	      }
         }
    drop bd_date_dum_1909_14
    drop bd_date_dum_1909_15
    drop bd_date_dum_1909_16
    drop bd_date_dum_1909_17
    drop bd_date_dum_1909_18


* Yield to maturity outcome variable

    gen YTM_Last_wis_bps = yield * 10000
    drop yield
    winsor YTM_Last_wis_bps, generate(yield) p(0.01)
    label variable yield "yield to maturity"

* Create maturity groups for interactions

    destring maturity_moodys09, gen(maturity_moodys09_num) force

    gen mat_yr_gp1 = 1 if maturity_moodys09_num<=1909
    replace mat_yr_gp1 = 2 if maturity_moodys09_num>=1910
    replace mat_yr_gp1 = 3 if maturity_moodys09_num>1911
    replace mat_yr_gp1 = 4 if maturity_moodys09_num>1913
    replace mat_yr_gp1 = 5 if maturity_moodys09_num>1915
    replace mat_yr_gp1 = 6 if maturity_moodys09_num>1920
    replace mat_yr_gp1 = 7 if maturity_moodys09_num>1925
    replace mat_yr_gp1 = 8 if maturity_moodys09_num>1930
    replace mat_yr_gp1 = 9 if maturity_moodys09_num>1940
    replace mat_yr_gp1 = 10 if maturity_moodys09_num>1950

*Create interactions of issuer characteristics with weeks since ratings

    local prod_vars `"factor_of_safety avg_income interest_per_mile duration PreMean_Spread_pct PreMean_YTM_Last"'
        foreach x of local prod_vars {
	       bys id_issuer_1: egen mn`x' = mean(`x')	
	       gen wks_mn`x' = weeks_since * mn`x'
        }

    *Assign shorter names for tables
    rename wks_mnPreMean_YTM_Last yield_wks
    rename wks_mnfactor_of_safety fsafety_wks
    rename wks_mnavg_income avginc_wks
    rename wks_mninterest_per_mile intrst_wks
    rename wks_mnduration duration_wks
    rename wks_mnPreMean_Spread_pct spread_wks


save "BondTransactionsAndRatingsWithSurprises.dta", replace
